package com.itmk.major.dao.impl;

import com.itmk.colloge.entity.TotalCologeVo;
import com.itmk.common.BaseDao;
import com.itmk.major.dao.MajorDao;
import com.itmk.major.entity.Major;
import com.itmk.major.entity.MajorParm;
import com.itmk.major.entity.MajorTotal;
import com.itmk.major.entity.MajorVo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class MajorDaoImpl extends BaseDao implements MajorDao {
    @Override
    public void add(Major major) {
        //1、定义sql
        String sql = "insert into major(major_id,colloge_id,major_name,order_num) values(?,?,?,?)";
        //2、定义handler
        BeanHandler handler = new BeanHandler(Major.class);
        //3、组装参数
        Object[] parms = {
                major.getMajorId(),
                major.getCollogeId(),
                major.getMajorName(),
                major.getOrderNum()
        };
        try {
            query.insert(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Major getById(String majorId) {
        try {
            return query.query("select * from major where major_id =?",new BeanHandler<>(Major.class,processor),majorId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateById(Major major) {
        //1、定义sql
        String sql = "update major set colloge_id=?,major_name=?,order_num=? where major_id=?";
        //2、封装参数
        Object[] parms = {
                major.getCollogeId(),
                major.getMajorName(),
                major.getOrderNum(),
                major.getMajorId()
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Major> list(MajorParm parm) {
        //关键字判断
        if(StringUtils.isNotEmpty(parm.getMajorName())) {
            //1、定义sql
            StringBuilder sb = new StringBuilder("select * from (select major.* ,ROWNUM rm from major order by order_num desc) ");
            sb.append(" where major_name like '%" + parm.getMajorName() + "%' ");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Major> handler = new BeanListHandler<>(Major.class,processor);
            try {
                return query.query(sql,handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else{
            StringBuilder sb = new StringBuilder("select * from (select major.* ,ROWNUM rm from major order by order_num desc) ");
            //排序
            sb.append(" where rm >= ? and rm<= ? ");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Major> handler = new BeanListHandler<>(Major.class,processor);
            //3、组装参数
            Object[] parms = {
                    (parm.getPage()-1)*parm.getLimit(),
                    (parm.getPage()*parm.getLimit())
            };
            try {
                return query.query(sql,handler,parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public int getCount(MajorParm parm) {
        //1、定义sql
        StringBuilder sb = new StringBuilder("select count(major_id) from  major ");
        //关键字
        if(StringUtils.isNotEmpty(parm.getMajorName())){
             sb.append(" where major_name like '%" + parm.getMajorName() + "%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler<>();
        try {
           return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public void delete(String majorId) {
        try {
            query.update("delete from major where major_id =?",majorId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Major> getMajorListBycollogeId(String collogeId) {
        //1、定义sql
        String sql = "select * from major where colloge_id=?";
        //2、定义handler
        BeanListHandler<Major> handler = new BeanListHandler<>(Major.class,processor);
        try {
            return query.query(sql,handler,collogeId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Major> getAll() {
       //1、定义sql
        String sql = "select * from major";
        //2、定义handler
        BeanListHandler<Major> handler = new BeanListHandler<>(Major.class,processor);
        try {
            return query.query(sql,handler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<MajorVo> majorCount() {
        //1、定义sql
        String sql = "select mj.major_name,count(st.stu_id) stu_count from major mj left join classes  cs on cs.major_id = mj.major_id left join student  st on st.calss_id = cs.calss_id group by st.stu_id,mj.major_name";
        //2、定义handler
        BeanListHandler<MajorVo> handler = new BeanListHandler<>(MajorVo.class,processor);
        try {
            return query.query(sql,handler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getTotal() {
        String sql = "select count(major_id) as major_total from major ";
         ScalarHandler handler = new ScalarHandler();
        try {
           return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
